-- Exercice 1

-- 1
SELECT Employes.Nom,Departements.Nom
FROM Employes
JOIN Departements ON Employes.idD=Departements.idD
WHERE DateEmbauche>='2020-01-01'

-- 2
SELECT Employes.Nom,Lieu
FROM Employes
JOIN Departements ON Employes.idD=Departements.idD
WHERE Lieu='Créteil'

-- 3
SELECT Employes.Nom
FROM Employes
JOIN Employes AS Chefs ON Employes.idEChef=Chefs.idE
JOIN Departements ON Employes.idD=Departements.idD
WHERE Chefs.Nom='Durand'
	AND Employes.Salaire>=35000

-- 4
SELECT MAX(Salaire)
FROM Employes
JOIN Departements ON Employes.idD=Departements.idD
WHERE Departements.Nom='Comptabilité'

-- 5
SELECT Nom
FROM Employes
WHERE Salaire<(SELECT MAX(Salaire) FROM Employes)/2


-- Exercice 2

-- 1
SELECT Nom
FROM Personnes
JOIN Occupants ON Personnes.idPersonne=Occupants.idPersonne
WHERE AnneeArrivee<1994

-- 2
SELECT Personnes.Nom
FROM Personnes
JOIN Occupants ON p.idPersonne=Occupants.idPersonne
JOIN Immeubles ON Occupants.idImmeuble=Immeubles.idImmeuble
WHERE Immeubles.Nom='Koudalou'

SELECT Personnes.Nom
FROM Personnes
JOIN Occupants USING(idPersonne)
JOIN Immeubles USING(idImmeuble)
WHERE Immeubles.Nom='Koudalou'

-- FROM		P 		I 		I 		O 		O 		P
-- JOIN		O 		O 		P 		I 		P 		I
-- JOIN		I 		P 		O 		P 		I 		O
--			Ok		Ok		Ko 		Ok		Ok		Ko

SELECT Personnes.Nom
FROM Occupants
JOIN Immeubles USING(idImmeuble)
JOIN Personnes ON Personnes.idPersonne=Occupants.idPersonne

-- 3
SELECT DISTINCT Immeubles.Nom
FROM Immeubles
JOIN Appartements ON Immeubles.idImmeuble=Appartements.idImmeuble
WHERE Superficie>150

-- 4
SELECT Gerants.Nom
FROM Immeubles
JOIN Occupants ON Immeubles.idImmeuble=Occupants.idImmeuble
JOIN Personnes AS Locataires ON Occupants.idPersonne=Locataires.idPersonne
JOIN Personnes AS Gerants ON Immeubles.idPersonne=Gerants.idPersonne
WHERE Locataires.Nom='Rachel'

-- 5
SELECT DISTINCT Immeubles.Nom
FROM Immeubles
JOIN Occupants ON Immeubles.idImmeuble=Occupants.idImmeuble
JOIN Personnes ON Occupants.idPersonne=Personnes.idPersonne
WHERE Profession='Acteur'

-- 6
SELECT Personnes.Nom
FROM Occupants
JOIN Appartements ON Occupants.idAppartement=Appartements.idAppartement
JOIN Personnes ON Occupants.idPersonne=Personnes.idPersonne
WHERE Superficie<70

-- 7
SELECT Nom,SUM(Superficie)
FROM Appartements
JOIN Immeubles ON Appartements.idImmeuble=Immeubles.idImmeuble
GROUP BY Appartements.idImmeuble

-- 8
SELECT COUNT(*)
FROM Immeubles
JOIN Appartements ON Immeubles.idImmeuble=Appartements.idImmeuble
JOIN Occupants ON Occupants.idAppartement=Appartements.idAppartement
JOIN Personnes ON Occupants.idPersonne=Personnes.idPersonne
WHERE Etage=NbEtages

-- 9
SELECT Personnes.Nom
FROM Immeubles
JOIN Occupants ON Occupants.idImmeuble=Immeubles.idImmeuble
JOIN Personnes ON Personnes.idPersonne=Occupants.idPersonne
WHERE AnneeArrivee>AnneeConstruction+20

-- 10
SELECT Personnes.Nom
FROM Immeubles
JOIN Appartements ON Immeubles.idImmeuble=Appartements.idImmeuble
JOIN Occupants ON Appartements.idAppartement=Occupants.idAppartement
JOIN Personnes ON Occupants.idPersonne=Personnes.idPersonne
WHERE NbEtages>10 AND Superficie>100

-- 11
SELECT Nom
FROM Immeubles
WHERE idImmeuble NOT IN
(
	SELECT DISTINCT idImmeuble
	FROM Occupants
	WHERE AnneeArrivee=1996
)

-- 12
SELECT Nom
FROM Immeubles
WHERE idImmeuble NOT IN
(
	SELECT DISTINCT idImmeuble
	FROM Occupants
	WHERE AnneeArrivee<>1994
)
AND
	idImmeuble IN
	(
		SELECT idImmeuble FROM Occupants
	)


-- Exercice 3

-- 1
SELECT Produits.Nom,Poids,Couleurs.Nom
FROM Produits
JOIN Couleurs USING(idCouleur)

-- 2
SELECT Fournisseurs.Nom
FROM Fournisseurs
JOIN Villes USING(idVille)
WHERE Villes.Nom='Reims'

-- 3
SELECT DISTINCT Fournisseurs.Nom
FROM Fournisseurs
JOIN Livraisons USING(idFournisseur)
JOIN Usines USING(idUsine)
JOIN Villes USING(idVille)
JOIN Produits USING(idProduit)
JOIN Couleurs USING(idCouleur)
WHERE Couleurs.Nom='Rouge' AND (Villes.Nom='Paris' OR Villes.Nom='Nantes')

-- 4
SELECT DISTINCT Produits.Nom
FROM Produits	
JOIN Livraisons USING(idProduit)
JOIN Fournisseurs USING(idFournisseur)
JOIN Villes AS v1 USING(idVille)
JOIN Usines USING(idUsine)
JOIN Villes AS v2 ON Usines.idVille=v2.idVille
WHERE v1.Nom='Reims' AND v2.Nom='Bordeaux'

-- 5
SELECT DISTINCT Produits.Nom
FROM Produits	
JOIN Livraisons USING(idProduit)
JOIN Fournisseurs USING(idFournisseur)
JOIN Usines USING(idUsine)
WHERE Fournisseurs.idVille=Usines.idVille

-- 6
SELECT AVG(Poids)
FROM Produits
JOIN Livraisons USING(idProduit)
JOIN Fournisseurs USING(idFournisseur)
WHERE Poids>1000
AND Fournisseurs.Nom='Sucre SA'

-- 7
SELECT AVG(Poids)
FROM Produits
JOIN Livraison USING(idProduit)
JOIN Usines USING(idUsine)
WHERE Usines.Nom='Meubles SARL'

-- 8
SELECT Usines.Nom
FROM Usines
JOIN Livraisons USING(idLivraison)
JOIN Fournisseurs USING(idFournisseur)
WHERE idUsine NOT IN
(
	SELECT DISTINCT Usines.idUsine
	FROM Usines
	JOIN Livraisons USING(idUsine)
	JOIN Fournisseurs USING(idFournisseur)
	WHERE Fournisseurs.Nom<>'Bois SA'
)

-- 9
SELECT Nom
FROM Fournisseurs
WHERE idFournisseur NOT IN
(
	SELECT DISTINCT idFournisseurs
	FROM Livraions
)